Imports System.Data.OracleClient

Public Class frmGenerator

  Private mArrTemplateModel() As String
  Private mObjClsGenClas As New clsGenClas
  Private mDrData As OracleDataReader
  Private mCmdData As OracleCommand

  Private mStrTableshrtName As String = String.Empty
  Private mStrTableDesc As String = String.Empty
  Private mStrClientIndc As String = String.Empty


  Private Sub frmGenerate_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Dim lStrDBStyle As String = String.Empty
    Dim lArrDBStyle As String()
    Dim objTags As New clsTags

    cboTables.Items.Clear()
    cboTables.Items.Add(ALL)
    objTags.fnSelectAllTables()
    While objTags.mDrTags.Read()
      cboTables.Items.Add(objTags.mDrTags("TABM_TABLE_NAME").ToString)
    End While
    cboTables.SelectedIndex = 0

    cboDBType.Items.Clear()
    cboDBType.Items.Add(ALL)
    mFnReadValue(TM_DATABASE_STYLE, String.Empty, "Oracle,SqlCE", lStrDBStyle, Application.StartupPath + "\" + DDL_TEMPLATE_FILE)
    lArrDBStyle = Split(Split(lStrDBStyle, Chr(0))(0), ",")
    For Each lStr As String In lArrDBStyle
      cboDBType.Items.Add(lStr)
    Next
    cboDBType.SelectedIndex = 0

    cboGnrtType.Items.Clear()
    cboGnrtType.Items.Add(ALL)
    mArrTemplateModel = mFnReadChildNodes(TM_TEMPLATE_MODEL, Application.StartupPath + "\" + CODE_TEMPLATE_FILE).Split(" ")
    For Each lStrValue As String In mArrTemplateModel
      If mFnReadAttribute(TM_TEMPLATE_MODEL, lStrValue, TM_CLIENT_INDC, Application.StartupPath + "\" + CODE_TEMPLATE_FILE) = CLIENT_INDC_BE _
      Or mFnReadAttribute(TM_TEMPLATE_MODEL, lStrValue, TM_CLIENT_INDC, Application.StartupPath + "\" + CODE_TEMPLATE_FILE) = CLIENT_INDC_FE _
      Or lStrValue = DDL Then
        cboGnrtType.Items.Add(lStrValue)
      End If
    Next
    cboGnrtType.SelectedIndex = 0

  End Sub

  Private Sub cboGnrtType_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboGnrtType.SelectedIndexChanged
    If UCase(cboGnrtType.Text.Trim) = ALL Or UCase(cboGnrtType.Text.Trim) = DDL Then
      cboDBType.Enabled = True
    Else
      cboDBType.Enabled = False
    End If
  End Sub

  Private Sub cmdClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdClose.Click
    Me.Close()
  End Sub

  Private Sub cmdGenerate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdGenerate.Click
    Dim lStrFolderPath As String = String.Empty
    Dim objTags As New clsTags

    If MsgBox("Do you want generate files for  " & cboTables.Text.Trim & " ?", MsgBoxStyle.YesNo Or MsgBoxStyle.Question, gCONST_MSG_HEADER) = MsgBoxResult.No Then _
    Exit Sub

    If SelectFolder(lStrFolderPath) = False Then Exit Sub

    Try
      If cboTables.Text.Trim = ALL Then
        objTags.fnSelectAllTables()
        While objTags.mDrTags.Read()
          mFnGenrate(objTags.mDrTags("TABM_TABLE_NAME").ToString.Trim, lStrFolderPath)
        End While
      Else
        mFnGenrate(cboTables.Text.Trim, lStrFolderPath)
      End If

      MsgBox("Generate successfully - in folder " & vbCrLf & "  " & lStrFolderPath, MsgBoxStyle.Information, gCONST_MSG_HEADER)
      Exit Sub
    Catch ex As Exception
      MsgBox("Error" & vbCrLf & ex.Message, MsgBoxStyle.Information, gCONST_MSG_HEADER)
      Exit Sub
    End Try

  End Sub

  Private Sub mFnGenrate(ByVal aStrTableName As String, ByVal aStrFolderPath As String)
    Dim lIntTableId As Integer

    lIntTableId = (New clsDBScriptGnrt).GetTableId(aStrTableName)
    PopulateColmuns(aStrTableName, lIntTableId)
    If UCase(cboGnrtType.Text.Trim) = ALL Then
      GenerateDDL(aStrTableName, ctlColumnsRecord, aStrFolderPath & "\" & DDL)

      For Each lStrValue As String In mArrTemplateModel
        If lStrValue.Trim <> ALL And lStrValue.Trim <> DDL Then
          If mFnReadAttribute(TM_TEMPLATE_MODEL, lStrValue, TM_CLIENT_INDC, Application.StartupPath + "\" + CODE_TEMPLATE_FILE) = CLIENT_INDC_BE _
          Or mFnReadAttribute(TM_TEMPLATE_MODEL, lStrValue, TM_CLIENT_INDC, Application.StartupPath + "\" + CODE_TEMPLATE_FILE) = CLIENT_INDC_FE Then
            GenerateCode(lStrValue.Trim, aStrTableName, Trim(mStrTableshrtName), Trim(mStrTableDesc), ctlColumnsRecord, aStrFolderPath)
          End If
        End If
      Next

    ElseIf UCase(cboGnrtType.Text.Trim) = DDL Then
      GenerateDDL(aStrTableName, ctlColumnsRecord, aStrFolderPath)
    Else
      GenerateCode(cboGnrtType.Text.Trim, aStrTableName, Trim(mStrTableshrtName), Trim(mStrTableDesc), ctlColumnsRecord, aStrFolderPath)
    End If

  End Sub

  Private Function GenerateDDL(ByVal aStrTableName As String, ByVal aDgvColmunDgv As DataGridView, ByVal aStrFolderPath As String) As String
    Dim lStrFileList As String = String.Empty

    If UCase(cboDBType.Text.Trim) = ALL Then
      For Each lStrItem As String In cboDBType.Items
        If UCase(lStrItem.Trim) <> ALL Then
          lStrFileList = lStrFileList & mObjClsGenClas.mFnGnrtDBScript(aStrTableName, lStrItem.Trim, aDgvColmunDgv, aStrFolderPath & "\" & lStrItem.Trim) & vbCrLf & "  "
        End If
      Next
    Else
      lStrFileList = lStrFileList & mObjClsGenClas.mFnGnrtDBScript(aStrTableName, cboDBType.Text.Trim, aDgvColmunDgv, aStrFolderPath) & vbCrLf & "  "
    End If
    Return lStrFileList
  End Function

  Private Function GenerateCode(ByVal aStrTemplateModel As String, ByVal aStrTableName As String, ByVal aStrTableShrtName As String, ByVal aStrTableDesc As String, ByVal aDgvColmunDgv As DataGridView, ByVal aStrFolderPath As String) As String
    Dim lStrFileList As String = String.Empty
    Dim lStrModels As String = String.Empty
    Dim lStrFileName As String = String.Empty
    Dim lStrModelName As String = String.Empty
    Dim lStrSaveClientIndc As String = String.Empty
    Dim lArrModels() As String

    mFnReadValue(TM_TEMPLATE_MODEL, aStrTemplateModel, String.Empty, lStrModels, Application.StartupPath + "\" + CODE_TEMPLATE_FILE)
    lStrSaveClientIndc = mFnReadAttribute(TM_TEMPLATE_MODEL, aStrTemplateModel, TM_CLIENT_INDC, Application.StartupPath + "\" + CODE_TEMPLATE_FILE)
    lArrModels = lStrModels.Split(",")

    For Each lStrModel As String In lArrModels
      lStrModelName = lStrModel.Split("|")(0)
      lStrFileName = lStrModel.Split("|")(1)
      lStrFileName = lStrFileName.Replace("%" & TM_TABLE_NAME & "%", StrConv(aStrTableName.Replace(TABLE_HEAD, String.Empty).Replace("_", " "), VbStrConv.ProperCase).Replace(" ", String.Empty))
      lStrFileList = lStrFileList & mObjClsGenClas.mFnGnrtCode(aStrTableName, aStrTableShrtName, aStrTableDesc, String.Empty, String.Empty, aDgvColmunDgv, aStrFolderPath & "\" & aStrTemplateModel, lStrFileName, lStrModelName, lStrSaveClientIndc) & vbCrLf & "  "
    Next

    Return lStrFileList
  End Function

  Private Function PopulateColmuns(ByVal aStrTableName As String, ByVal aIntTableId As Integer) As Boolean
    Dim lStrQuery As String = String.Empty
    Dim lIntRowIndex As Integer

    mPrRefreshConn()
    lStrQuery = "SELECT TABM_CLNT_SRVR_INDC,TABM_TABLE_SHORT_NAME,TTD_TABLE_DESC FROM TRDX_TABLES_MSTR,TRDX_TABLES_DTLS WHERE TABM_TABLE_NAME = '" + UCase(aStrTableName) + "'"
    lStrQuery = lStrQuery & " AND TABM_TABLE_ID  = " & aIntTableId
    lStrQuery = lStrQuery & " AND TTD_TABLE_ID=TABM_TABLE_ID"
    mCmdData = New OracleCommand(lStrQuery, con)
    mDrData = mCmdData.ExecuteReader

    If mDrData.HasRows Then
      While mDrData.Read
        mStrTableshrtName = mDrData.Item("TABM_TABLE_SHORT_NAME")
        mStrTableDesc = mDrData.Item("TTD_TABLE_DESC")
        mStrClientIndc = mDrData.Item("TABM_CLNT_SRVR_INDC")
      End While
    Else
      mStrTableshrtName = String.Empty
      mStrTableDesc = String.Empty
      mStrClientIndc = String.Empty
      MsgBox("Error", MsgBoxStyle.Information, gCONST_MSG_HEADER)
      Exit Function
    End If

    lStrQuery = "SELECT * FROM TRDX_TABLE_COLUMNS"
    lStrQuery = lStrQuery & " WHERE TAB_TABLE_ID = " & aIntTableId
    mCmdData = New OracleCommand(lStrQuery, con)
    mDrData = mCmdData.ExecuteReader

    If mDrData.HasRows Then
      ctlColumnsRecord.Rows.Clear()
      While mDrData.Read
        lIntRowIndex = ctlColumnsRecord.Rows.Add
        With ctlColumnsRecord.Rows(lIntRowIndex)
          .Cells("clmSortId").Value = CInt(mDrData.Item("TAB_COL_ID"))
          .Cells("clmClmName").Value = mDrData.Item("TAB_COL_NAME")
          .Cells("clmNull").Value = mDrData.Item("TAB_COL_NULL_INDC")
          .Cells("clmLength").Value = mDrData.Item("TAB_COL_DATA_LENGTH")
          .Cells("clmPrecision").Value = mDrData.Item("TAB_COL_DATA_PRECISION")
          .Cells("clmBasicDT").Value = mDrData.Item("TAB_COL_BASIC_DATA_TYPE")
          .Cells("clmFECategory").Value = mDrData.Item("TAB_COL_DATA_TYPE")
          .Cells("clmClientSrvIndc").Value = mStrClientIndc
          .Cells("clmVariableReference").Value = mDrData.Item("tab_vrbl_serial_nmbr")
          GetDataCategory(Replace(mDrData.Item("TAB_COL_DATA_TYPE").ToString, "cls", String.Empty), _
                          .Cells("clmFEDataType").Value, _
                          .Cells("clmBEDataType").Value, _
                          .Cells("clmClmType").Value, _
                          .Cells("clmClmTypeValueMember").Value, _
                          .Cells("clmBEDBDataType").Value)
        End With
      End While
    Else
      ctlColumnsRecord.Rows.Clear()
      MsgBox("Error", MsgBoxStyle.Information, gCONST_MSG_HEADER)
      Exit Function
    End If

    lStrQuery = "SELECT * FROM TRDX_TABL_INDX T "
    lStrQuery = lStrQuery & " WHERE T.TTL_TABL_IDXX = " & aIntTableId
    lStrQuery = lStrQuery & " AND T.TTL_INDX_TYPE='P' "
    mCmdData = New OracleCommand(lStrQuery, con)
    mDrData = mCmdData.ExecuteReader
    If mDrData.HasRows Then
      While mDrData.Read
        For Each lObjRow As DataGridViewRow In ctlColumnsRecord.Rows
          If mDrData.Item("TTL_COLM_IDXX") = lObjRow.Cells("clmSortId").Value Then
            lObjRow.Cells("clmPK").Value = "Y"
          End If
        Next
      End While
    End If

    ctlColumnsRecord.Sort(ctlColumnsRecord.Columns("clmSortId"), System.ComponentModel.ListSortDirection.Ascending)
  End Function

  Private Sub GetDataCategory(ByVal aStrFECategory As String _
                             , ByRef aStrFEDT As String _
                             , ByRef aStrBEDT As String _
                             , ByRef aStrDataDesc As String _
                             , ByRef aStrDataCategory As String _
                             , ByRef aStrBEDBDataType As String)
    Dim lStrQuery As String = String.Empty
    Dim lCmdData As OracleCommand
    Dim lDrData As OracleDataReader

    mPrRefreshConn()
    lStrQuery = "SELECT * FROM TRDX_DATA_CATEGORY_MSTR T WHERE T.TDCM_FRNT_END_DATA_CATEGORY='" & aStrFECategory & "'"
    lCmdData = New OracleCommand(lStrQuery, con)
    lDrData = lCmdData.ExecuteReader
    If mDrData.HasRows Then
      While lDrData.Read
        aStrFEDT = lDrData.Item("TDCM_FRNT_END_DATA_TYPE")
        aStrBEDT = lDrData.Item("TDCM_BACK_END_DATA_TYPE")
        aStrDataDesc = lDrData.Item("TDCM_DESC")
        aStrDataCategory = lDrData.Item("TDCM_DATA_CTGRY")
        aStrBEDBDataType = lDrData.Item("TDCM_DATABASE_DATA_TYPE")
      End While
    Else
      aStrFEDT = String.Empty
      aStrBEDT = String.Empty
      aStrDataDesc = String.Empty
      aStrDataCategory = String.Empty
      aStrBEDBDataType = String.Empty
    End If
  End Sub

End Class